CREATE DATABASE [HelenaImport]
GO

USE [HelenaImport]
GO

CREATE TABLE [dbo].[tblSampleHeader](
	[SampleHeaderUID] [int] IDENTITY(1,1) NOT NULL CONSTRAINT [PK_tblSampleHeader] PRIMARY KEY,
	[PlateBarCode] [varchar] (20)  NOT NULL,
	[PlateFileUploadedOn] [datetime] NOT NULL,
	[ProcessedBy] [varchar] (64) NULL,	
	[ProcessedOn] [datetime] NULL
)

CREATE UNIQUE INDEX UIX_SampleHeader_PlateBarCode_PlateFileUploadedOn ON [dbo].[tblSampleHeader]([PlateBarCode], [PlateFileUploadedOn]) 
 
CREATE TABLE [dbo].[tblSampleDetail](	
	[SourcePositionBarCode] [varchar](30) NOT NULL,
	[SampleHeaderUID] int NOT NULL,
	[PatientCholesterolValue] int NULL,
	[TargetPositionID] [int] NULL,
	[TargetVolume] [int] NULL,
	[TargetSumStateDesc] [varchar](128) NULL,
	[SourcePositionID] [int] NULL,
	[SampleProcessedBy] [varchar] (64) NULL,	
	[SampleProcessedOn] [datetime] NULL,
 CONSTRAINT [PK_tblSampleDetail] PRIMARY KEY
	([SourcePositionBarCode], [SampleHeaderUID] ASC
)
)
GO

ALTER TABLE [dbo].[tblSampleDetail]  WITH CHECK ADD  CONSTRAINT [FK_tblSampleDetail_tblSampleHeader] FOREIGN KEY([SampleHeaderUID])
REFERENCES [dbo].[tblSampleHeader] ([SampleHeaderUID])
GO
ALTER TABLE [dbo].[tblSampleDetail] CHECK CONSTRAINT [FK_tblSampleDetail_tblSampleHeader]


INSERT INTO [tblSampleHeader] ([PlateBarCode],[PlateFileUploadedOn],[ProcessedBy],[ProcessedOn])
     VALUES('120710.1','07-10-2012 16:17:38','','07-10-2012 16:17:38')
INSERT INTO [tblSampleHeader] ([PlateBarCode],[PlateFileUploadedOn],[ProcessedBy],[ProcessedOn])
     VALUES('120710.1','07-10-2012 16:17:39','','07-10-2012 16:17:38')
INSERT INTO [tblSampleHeader] ([PlateBarCode],[PlateFileUploadedOn],[ProcessedBy],[ProcessedOn])
     VALUES('120710.1','07-10-2012 16:17:40','','07-10-2012 16:17:38')
INSERT INTO [tblSampleHeader] ([PlateBarCode],[PlateFileUploadedOn],[ProcessedBy],[ProcessedOn])
     VALUES('120710.1','07-10-2012 16:17:41','','07-10-2012 16:17:38')
GO
